package Dao;
//BaseDao封装全部的对数据库操作的方法，所有表对应的类要想查询都要继承这个类，
//并且这个类位抽象类，不可以实例化
import DBUtils.DBUtils;
import bean.User;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


import java.sql.Connection;
import java.util.List;

public abstract class BaseDao {

    public int testQueryRunnerUpdate(String sql,User user) {
        Connection conn = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            conn = DBUtils.getConnByDruid();
//            String sql = "insert into user_tab (id,name,balance) values(?,?,?)";
            int count = queryRunner.update(conn, sql, user.getId(),user.getName(), user.getBalance());
            return count;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.closeSource(conn,null);
        }
        return 0;
    }


    public int testQueryRunnerDelete(String sql,int id) {
        Connection conn = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            conn = DBUtils.getConnByDruid();
//            String sql = "insert into user_tab (id,name,balance) values(?,?,?)";
            int count = queryRunner.update(conn, sql, id);
            return count;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.closeSource(conn,null);
        }
        return 0;
    }

    //使用QueryRunner实现数据的单条查询
    public User testQueryRunnerSelectOneInfo(int id,String sql)  {
        Connection conn = null;
        try{
            conn = DBUtils.getConnByDruid();
            QueryRunner queryRunner = new QueryRunner();
//            String sql = "select id,name,balance from user_tab where id = ?";
            BeanHandler beanHandler = new BeanHandler<User>(bean.User.class);
            Object query = queryRunner.query(conn, sql, beanHandler, id);
            return (User)query;
//            System.out.println(query);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.closeSource(conn,null);
        }
        return null;
    }

    //使用QueryRunner实现查询多条语句
    public List<User> testQueryRunnerSelectManyInfo(String sql,int pageNum){
        Connection conn = null;
        try{
            conn = DBUtils.getConnByDruid();
            QueryRunner queryRunner = new QueryRunner();
            BeanListHandler<User> listHandler = new BeanListHandler<>(User.class);
            List<User> queryList = queryRunner.query(conn, sql, listHandler,pageNum);
            return queryList;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.closeSource(conn,null);
        }
        return null;
    }

    //使用QueryRunner实现特殊查询
    public <T> T testQueryRunnerSelectSpeciInfo(String sql,String keyWord) {
        Connection conn = null;
        try{
            conn = DBUtils.getConnByDruid();
            QueryRunner queryRunner = new QueryRunner();
//            String sql = "select count(*) from user_tab";
            ScalarHandler<Object> handler = new ScalarHandler<>();
            T query = (T) queryRunner.query(conn, sql, handler,keyWord);
//            System.out.println("user表中有"+query+"条数据");
            return (T) query;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //悄悄的关闭资源
            DBUtils.closeSource(conn,null);
        }
        return null;
    }

    //使用QueryRunner实现查询多条语句
    public List<User> testQueryRunnerSelectManyInfo(String sql,String keyWord,int pageNum){
        Connection conn = null;
        try{
            conn = DBUtils.getConnByDruid();
            QueryRunner queryRunner = new QueryRunner();
            BeanListHandler<User> listHandler = new BeanListHandler<>(User.class);
            List<User> queryList = queryRunner.query(conn, sql, listHandler,keyWord,pageNum);
            return queryList;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.closeSource(conn,null);
        }
        return null;
    }




    public <T> T testQueryRunnerSelectSpeciInfo(String sql) {
        Connection conn = null;
        try{
            conn = DBUtils.getConnByDruid();
            QueryRunner queryRunner = new QueryRunner();
//            String sql = "select count(*) from user_tab";
            ScalarHandler<Object> handler = new ScalarHandler<>();
            T query = (T) queryRunner.query(conn, sql, handler);
//            System.out.println("user表中有"+query+"条数据");
            return (T) query;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //悄悄的关闭资源
            DBUtils.closeSource(conn,null);
        }
        return null;
    }
}
